/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.anniversary.repository;

import me.zhengjie.modules.anniversary.domain.StatisticsInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
* @website https://el-admin.vip
* @author Jiayin Wei
* @date 2020-07-27
**/
public interface StatisticsInfoRepository extends JpaRepository<StatisticsInfo, Long>, JpaSpecificationExecutor<StatisticsInfo> {
    /**
     * 查询学生校友性别分布信息
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            "( SELECT CASE WHEN gender IS NULL THEN 2 ELSE gender END AS gender, COUNT(1) AS count" +
            " FROM v_student_education GROUP BY gender ) AS t2" +
            " WHERE t1.dict_id=9 AND t1.`value`=t2.gender" +
            " ORDER BY t1.`value`", nativeQuery = true)
    List<StatisticsInfo> gender();

    /**
     * 查询学生校友性别分布信息(根据学院)
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            "( SELECT CASE WHEN gender IS NULL THEN 2 ELSE gender END AS gender, COUNT(1) AS count" +
            " FROM v_student_education WHERE college=:college GROUP BY gender ) AS t2" +
            " WHERE t1.dict_id=9 AND t1.`value`=t2.gender" +
            " ORDER BY t1.`value`", nativeQuery = true)
    List<StatisticsInfo> genderByCollege(@Param("college") String college);

    /**
     * 查询学生校友民族分布信息
     * @param top
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            "( SELECT nation_id, COUNT(1) AS count FROM v_student_education" +
            " WHERE nation_id IS NOT NULL GROUP BY nation_id) AS t2" +
            " WHERE t1.dict_id=19 AND t1.`value`=t2.nation_id" +
            " ORDER BY count DESC limit :top", nativeQuery = true)
    List<StatisticsInfo> nation(@Param("top") Integer top);

    /**
     * 查询学生校友民族分布信息(根据学院)
     * @param top
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            "( SELECT nation_id, COUNT(1) AS count FROM v_student_education" +
            " WHERE nation_id IS NOT NULL AND college=:college GROUP BY nation_id) AS t2" +
            " WHERE t1.dict_id=19 AND t1.`value`=t2.nation_id" +
            " ORDER BY count DESC limit :top", nativeQuery = true)
    List<StatisticsInfo> nationByCollege(@Param("college") String college, @Param("top") Integer top);

    /**
     * 查询学生校友政治面貌分布信息
     * @param top
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            "( SELECT political_id, COUNT(1) AS count FROM v_student_education" +
            " WHERE political_id IS NOT NULL GROUP BY political_id ) AS t2" +
            " WHERE t1.dict_id=18 AND t1.`value`=t2.political_id" +
            " ORDER BY t1.`value` limit :top", nativeQuery = true)
    List<StatisticsInfo> political(@Param("top") Integer top);

    /**
     * 查询政学生校友治面貌分布信息(根据学院)
     * @param top
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            "( SELECT political_id, COUNT(1) AS count FROM v_student_education" +
            " WHERE political_id IS NOT NULL AND college=:college GROUP BY political_id ) AS t2" +
            " WHERE t1.dict_id=18 AND t1.`value`=t2.political_id" +
            " ORDER BY t1.`value` limit :top", nativeQuery = true)
    List<StatisticsInfo> politicalByCollege(@Param("college") String college, @Param("top") Integer top);

    /**
     * 查询教师校友性别分布信息
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            " ( SELECT IFNULL(gender, 2) AS gender, COUNT(1) AS count " +
            " FROM gzmu_ann_teacher GROUP BY gender ) AS t2 " +
            " WHERE t1.dict_id=9 AND t1.`value`=t2.gender" +
            " ORDER BY t1.`value`", nativeQuery = true)
    List<StatisticsInfo> genderTeacher();

    /**
     * 查询教师校友民族分布信息
     * @param top
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            " ( SELECT nation_id, COUNT(1) AS count FROM gzmu_ann_teacher" +
            " WHERE nation_id IS NOT NULL GROUP BY nation_id ) AS t2" +
            " WHERE t1.dict_id=19 AND t1.`value`=t2.nation_id" +
            " ORDER BY count DESC limit :top", nativeQuery = true)
    List<StatisticsInfo> nationTeacher(@Param("top") Integer top);

    /**
     * 查询教师校友政治面貌分布信息
     * @param top
     * @return
     */
    @Query(value = "SELECT t1.label AS name, t2.count FROM sys_dict_detail AS t1, " +
            " ( SELECT political_id, COUNT(1) AS count FROM gzmu_ann_teacher" +
            " WHERE political_id IS NOT NULL GROUP BY political_id ) AS t2" +
            " WHERE t1.dict_id=18 AND t1.`value`=t2.political_id" +
            " ORDER BY t1.`value` limit :top", nativeQuery = true)
    List<StatisticsInfo> politicalTeacher(@Param("top") Integer top);

    /**
     * 查询教师校友行业分布信息
     * @param top
     * @return
     */
    @Query(value = "SELECT industry AS name, COUNT(1) AS count FROM gzmu_ann_teacher GROUP BY industry" +
            " limit :top", nativeQuery = true)
    List<StatisticsInfo> industryTeacher(@Param("top") Integer top);

    /**
     * 查询教师校友行业分布信息
     * @param top
     * @return
     */
    @Query(value = "SELECT name, count FROM v_dept_teacher_statistic WHERE pid=3" +
            " limit :top", nativeQuery = true)
    List<StatisticsInfo> deptTeacher(@Param("top") Integer top);
}
